CREATE TABLE breeds
(
    id NUMERIC(3) PRIMARY KEY,
    description VARCHAR(20)
);

CREATE TABLE houses
(
    id NUMERIC(1) PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE subjects
(
    id NUMERIC(2) PRIMARY KEY,
    description VARCHAR(30) NOT NULL
);

CREATE TABLE characters
(
    id NUMERIC(3) PRIMARY KEY,
    name VARCHAR(20),
    surname VARCHAR(30),
    sex CHAR,
    title VARCHAR(10),
    suffix VARCHAR(2),
    alias VARCHAR(20),
    breed NUMERIC(2),
    death_eater BIT,
    OP_member BIT,
    FOREIGN KEY(breed) REFERENCES breeds(id)
);

CREATE TABLE teaches
(
    teacher NUMERIC(3) NOT NULL,
    subject NUMERIC(2) NOT NULL,
    started DATE,
    ended DATE,
    PRIMARY KEY(teacher,subject),
    FOREIGN KEY(teacher) REFERENCES characters(id),
    FOREIGN KEY(subject) REFERENCES subjects(id)
);

CREATE TABLE studies
(
    student NUMERIC(3) PRIMARY KEY,
    started DATE,
    ended DATE,
    house NUMERIC(1),
    FOREIGN KEY(student) REFERENCES characters(id),
    FOREIGN KEY(house) REFERENCES houses(id)
);

CREATE TABLE is_headmaster
(
    teacher NUMERIC(3) PRIMARY KEY,
    started DATE,
    ended DATE,
    FOREIGN KEY(teacher) REFERENCES characters(id)
);

CREATE TABLE is_head_of_house
(
    teacher NUMERIC(3),
    house NUMERIC(1),
    started DATE,
    ended DATE,
    PRIMARY KEY(teacher,house),
    FOREIGN KEY(teacher) REFERENCES characters(id),
    FOREIGN KEY(house) REFERENCES houses(id)
);